import pandas as pd
from tabulate import tabulate
from datetime import datetime
class MainVm:
    weekdaysCn = ['周一', '周二', '周三', '周四', '周五', '周六', '周日']
    # 指定年月
    # year = 2025
    # month = 9  # 例如：9月
    # 创建该月每一天的日期范围
   
    # year = 2025
    # month = 9  # 例如：9月
    def __init__(self):
        print("new MainVm init")
    
    def getMoreDate(self,filePath,tableName):
        if self.isStrEmpty(tableName):
            return []
        sheetNameArr = [tableName]
        df = pd.read_excel(filePath, sheet_name=sheetNameArr)
        for dfItem in df:
            df_melted = df[dfItem].melt(id_vars='日期',var_name='星期', value_name='对应周')
        df_melted.pop('星期')   
        # result = [tuple(x) for x in df_melted.values]
        print("融化后的长表：")
        moreDate = []
        for row in df_melted.itertuples():
            weekDayNum = 0
            match row.对应周:
                case '周一':
                    weekDayNum = 0
                case '周二':
                    weekDayNum = 1
                case '周三':
                    weekDayNum = 2
                case '周四': 
                    weekDayNum = 3
                case '周五':
                    weekDayNum = 4
                case '周六':
                    weekDayNum = 5
                case '周日':
                    weekDayNum = 6
            moreDate.append((row.日期, row.对应周, weekDayNum))
        # print(moreDate)
        return moreDate
    
    # 判断日期是否在
    def checkMoreDate(self,moreDate,date):
        for item in moreDate:
            # print(f"date :{date} - {item[0]}")
            if date == item[0]:
                return item[2]
        return -1

    # 获取当前月的日子对应的星期
    def getMonthOrWeekArr(self, dateTypeSelected,yearOne,monthOne,dateMoreStart,dateMoreEnd,moreDate):
        print("getMonthArr start")
        if dateTypeSelected == "one":
            dates = pd.date_range(start=f'{yearOne}-{monthOne}-01', 
                        end=f'{yearOne}-{monthOne+1}-01', 
                        freq='D')[:-1]
        else:
            dates = pd.date_range(start=dateMoreStart, end=dateMoreEnd, freq='D')
        # print(dates)
        result = []
        for date in dates:
            year = date.year
            month = date.month
            day = date.day
            weekday_name = date.day_name()    # 星期名称，如 'Monday'
            weekday_num = date.dayofweek      # 星期编号：0=Monday, 6=Sunday
            result.append((year,month,day, weekday_name, weekday_num))
        # for p in result:
        #     print(p)
        monthArr = []
        for year,month,day, dayName, num in result:
            monthOneDay = []
            moreNum = self.checkMoreDate(moreDate,pd.to_datetime(f"{year}-{month:02d}-{day:02d}"))
            if moreNum >= 0 : 
                # print(moreNum)
                monthOneDay.append(MainVm.weekdaysCn[moreNum])
            else:
                monthOneDay.append(MainVm.weekdaysCn[num])
            monthOneDay.append(f"{year}-{month:02d}-{day:02d}")
            monthArr.append(monthOneDay)
        # for p in monthArr:
        #     print(p)
        return monthArr

    def fileTable(self,filePath,tableName):
        sheetNameArr = [tableName]
        df = pd.read_excel(filePath, sheet_name=sheetNameArr)
        for dfItem in df:
            df_melted = df[dfItem].melt(id_vars='班级', var_name='星期', value_name='老师')
        # 补齐合并格拆分的班级
        df_melted['班级'] = df_melted['班级'].fillna(method='ffill')
        # print("融化后的长表：")
        # print(df_melted)
         # 将 'Name' 列移到最前面（索引 0）
        col = df_melted.pop('星期')        # 删除该列并返回
        df_melted.insert(0, '星期', col)   # 在位置 0 插入
        # print("移动星期到最前面表：")
        # print(df_melted)
        
        # 转为元组列表
        result = [tuple(x) for x in df_melted.values]
        # print(dfItem+"_最终表：")
        # for item in result:
        #     print(item)
        return result
    
    def patrolTable(self,filePath,tableName):
        sheetNameArr = [tableName]
        df = pd.read_excel(filePath, sheet_name=sheetNameArr)
        # print(df)
        for dfItem in df:
            df_melted = df[dfItem]
        # print(df_melted)
        return df_melted
        # counts = df_melted['行政巡堂'].value_counts()
        # result = counts.reset_index()
        # result.columns = ['姓名', '出现次数']  # 重命名列
        # print(result)
        # return result

    def statisticsTable(self,fileDateName, monthArr, tableResult, patrolArr):
        ymData = []
        for mItem in monthArr:
            weekDayName = mItem[0]
            ymdName = mItem[1]
            
            for cItem in tableResult:
                wdName = cItem[0]
                wdGradeName = cItem[1]
                wdTeacherName = cItem[2]
                ymOneData = []
                if wdName == weekDayName:
                    ymOneData.append(ymdName) 
                    ymOneData.append(weekDayName) 
                    ymOneData.append(wdGradeName) 
                    ymOneData.append(wdTeacherName)
                    ymData.append(ymOneData)

        for row in patrolArr.itertuples():
            ymOneData = []
            ymOneData.append('未知日期')
            ymOneData.append("行政巡堂")
            ymOneData.append('领导')
            ymOneData.append(row.行政巡堂)
            ymData.append(ymOneData)

        dfYm = pd.DataFrame(ymData, columns=['ymdName', 'weekDayName', 'wdGradeName','wdTeacherName'])

        print(dfYm)
        tCounts = pd.crosstab(dfYm['weekDayName'],dfYm['wdTeacherName'],margins=True,margins_name='Total')
        # 把列和行标题调转
        tCounts = tCounts.transpose()
        # print(tCounts)
        # print(tabulate(tCounts, headers='keys', tablefmt='psql', showindex=True))
        nowTimeStr = datetime.now()
        fileName = nowTimeStr.strftime("%Y%m%d%H%M%S")
        tCounts.to_excel(f'统计_{fileDateName}_{fileName}.xlsx', sheet_name='统计', index=True)
        return tCounts
    
    def startOne(self,filePath,fileTabMainName,fileTabMoreName,fileTabPatrolName,
                dateTypeSelected,
                yearOne,monthOne,
                dateMoreStart,dateMoreEnd):
        moreDate = self.getMoreDate(filePath,fileTabMoreName)
        
        monthArr = self.getMonthOrWeekArr(dateTypeSelected,yearOne,monthOne,
                dateMoreStart,dateMoreEnd,moreDate)

        tabelResult = self.fileTable(filePath,fileTabMainName)

        if self.isStrEmpty(fileTabPatrolName):
            patrolArr = pd.DataFrame({})
        else:
            patrolArr = self.patrolTable(filePath, fileTabPatrolName)

        fileDateName = f"{fileTabMainName}"
        if dateTypeSelected == "one":
            fileDateName = f"{fileDateName}-{yearOne}-{monthOne}"
        else:
            fileDateName = f"{fileDateName}-{dateMoreStart}-{dateMoreEnd}"

        return self.statisticsTable(fileDateName,monthArr,tabelResult, patrolArr)
    
    def isStrEmpty(self,strValue):
        return isinstance(strValue, str) and not strValue.strip()

    
# ========= 主程序入口 =========
# if __name__ == "__main__":
#     vm = MainVm()

#     moreDate = vm.getMoreDate("E:\WordSpace\Python\Demo\data.xlsx","more")
#     monthArr = vm.getMonthOrWeekArr(2025,9,moreDate)
#     tabelResult = vm.fileTable("E:\WordSpace\Python\Demo\data.xlsx","test")
#     ss = vm.statisticsTable("jkl",monthArr,tabelResult)

    # sheetNameArr = ["tt"]
    # df = pd.read_excel("E:\WordSpace\Python\Demo\data.xlsx", sheet_name=sheetNameArr)
    # for dfItem in df:
    #     df_melted = df[dfItem].melt(id_vars='班级', var_name='星期', value_name='老师')
    # df_melted['班级'] = df_melted['班级'].fillna(method='ffill')
    # print("融化后的长表：")
    # print(df_melted)

    # vv = vm.patrolTable("E:\WordSpace\Python\Demo\data.xlsx","ss")